package br.com.hmm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import br.com.hmm.businesslogic.ContaService;
import br.com.hmm.businesslogic.ItensCategoriaService;
import br.com.hmm.entidade.Transacoes;

public class TransacoesDAO {
	
	private Connection conn = null;
	private static final String CREATE_TRANSACAO = "INSERT INTO TRANSACOES (TRN_ID, TRN_TITULO, TRN_VALOR, TRN_DATA, TRN_DESCRICAO, TRN_TIPO, CONTA_CON_ID, ITENS_CATEGORIA_ITN_ID) VALUES(NULL, ?, ?, ?, ?, ?, ?, ?);";
	private static final String UPDATE_TRANSACAO = "UPDATE TRANSACOES SET TRN_TITULO = ?, TRN_VALOR = ?, TRN_DATA = ?, TRN_DESCRICAO = ?, TRN_TIPO = ?, ITENS_CATEGORIA_ITN_ID = ? WHERE TRN_ID = ?";
	private static final String DELETE_TRANSACAO = "DELETE FROM TRANSACOES WHERE TRN_ID = ? ;";
	private static final String FIND_BY_CONTA	 = "SELECT TRN_ID, TRN_TITULO, TRN_VALOR, TRN_DATA, TRN_DESCRICAO, TRN_TIPO, CONTA_CON_ID, ITENS_CATEGORIA_ITN_ID FROM TRANSACOES WHERE CONTA_CON_ID = ? ;  ";
	private static final String FILTRO_MES		 = "SELECT  t.TRN_ID, t.TRN_TITULO, t.TRN_VALOR, t.TRN_DATA,t.TRN_DESCRICAO, t.TRN_TIPO, CONTA_CON_ID, t.ITENS_CATEGORIA_ITN_ID FROM TRANSACOES t WHERE MONTH(t.TRN_DATA) = ? AND CONTA_CON_ID = ?";
	private static final String PAG_FAZER 		 = "SELECT TRN_ID, TRN_VALOR, TRN_TITULO, TRN_DATA, DATEDIFF(TRN_DATA,CURDATE()) AS 'DIAS FALTANTES', LAST_DAY(NOW()) AS 'ULTIMO_DIA', CONTA_CON_ID FROM TRANSACOES WHERE TRN_TIPO = 'despesa' AND DATEDIFF(TRN_DATA,CURDATE()) > 0 AND NOW() <>  LAST_DAY(NOW()) AND CONTA_CON_ID = ?  ;" ;
	private static final String LANC_FUTURO		 = "SELECT TRN_ID, TRN_VALOR, TRN_TITULO, TRN_DATA, DATEDIFF(TRN_DATA,CURDATE()) AS 'DIAS FALTANTES', LAST_DAY(NOW()) AS 'ULTIMO_DIA', CONTA_CON_ID FROM TRANSACOES WHERE TRN_TIPO = 'entrada' AND DATEDIFF(TRN_DATA,CURDATE()) > 0 AND NOW() <>  LAST_DAY(NOW()) AND CONTA_CON_ID = ? ;";
	//private static final String FIND_BY_CATEGORIA= "";
	private Transacoes tr;
	private int dias;
	
	public TransacoesDAO(){
		tr = new Transacoes();
		dias = 0;
	}
	


	public Transacoes getTr() {
		return tr;
	}



	public void setTr(Transacoes tr) {
		this.tr = tr;
	}



	public int getDias() {
		return dias;
	}

	public void setDias(int dias) {
		this.dias = dias;
	}

	public boolean inserir(Transacoes trn) throws Exception{
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(CREATE_TRANSACAO);
			stmt.setString(1, trn.getTitulo());
			stmt.setDouble(2, trn.getValor());
			stmt.setDate(3, new java.sql.Date(trn.getData().getTime()));
			stmt.setString(4, trn.getDescricao());
			stmt.setString(5, trn.getTipo());
			stmt.setInt(6, trn.getConta().getId());
			stmt.setInt(7, trn.getItemCategoria().getId());
			if(stmt.executeUpdate() > 0){
				return true;
			}
			
		}catch(Exception e){
			throw new Exception(e.getMessage());
		}finally{
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}

	
	public boolean atualizar(Transacoes trn){
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(UPDATE_TRANSACAO);
			stmt.setString(1, trn.getTitulo());
			stmt.setDouble(2, trn.getValor());
			stmt.setDate(3, new java.sql.Date(trn.getData().getTime()));
			stmt.setString(4, trn.getDescricao());
			stmt.setString(5, trn.getTipo());
			stmt.setInt(6, trn.getItemCategoria().getId());
			stmt.setInt(7, trn.getId());
			if(stmt.executeUpdate() > 0){
				return true;
			}
			
		}catch(Exception e){
			return false;
		}finally{
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}
	
	
	public boolean delete(Transacoes trn){
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(DELETE_TRANSACAO);
			stmt.setInt(1, trn.getId());
			if(stmt.executeUpdate() > 0){
				return true;
			}
		
		}catch(Exception e){
			return false;
		}finally{
			DAOUtil.close(this.conn, stmt);
		}
		return false;
		
	}
	
	
	public List<Transacoes> findByConta(int id) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(FIND_BY_CONTA);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			List<Transacoes> lt = new ArrayList<Transacoes>();
			while(rs.next()){
				Transacoes t = new Transacoes();
				t.setId(rs.getInt(1));
				t.setTitulo(rs.getString(2));
				t.setValor(rs.getDouble(3));
				t.setData(rs.getDate(4));
				t.setDescricao(rs.getString(5));
				t.setTipo(rs.getString(6));
				t.setConta(ContaService.findById(rs.getInt(7)));
				t.setItemCategoria(ItensCategoriaService.findById(rs.getInt(8)));
				lt.add(t);
			}
			if(!lt.isEmpty()){
				return lt;
			}
			
		}catch (Exception e) {
			throw new Exception("Não foi possível recuperar os dados do banco." + e.getLocalizedMessage());
		}finally{
			DAOUtil.close(this.conn, stmt, rs);
			
		}
		
		return null;
	}
	
	public List<Transacoes> filtrarPorMes(int idConta,int mes) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(FILTRO_MES);
			stmt.setInt(1, mes);
			stmt.setInt(2, idConta);
			rs = stmt.executeQuery();
			List<Transacoes> lt = new ArrayList<Transacoes>();
			while(rs.next()){
				Transacoes t = new Transacoes();
				t.setId(rs.getInt(1));
				t.setTitulo(rs.getString(2));
				t.setValor(rs.getDouble(3));
				t.setData(rs.getDate(4));
				t.setDescricao(rs.getString(5));
				t.setTipo(rs.getString(6));
				t.setConta(ContaService.findById(rs.getInt(7)));
				t.setItemCategoria(ItensCategoriaService.findById(rs.getInt(8)));
				lt.add(t);
			}
			if(!lt.isEmpty()){
				return lt;
			}
			
		}catch (Exception e) {
			throw new Exception("Não foi possível recuperar os dados do banco." + e.getLocalizedMessage());
		}finally{
			DAOUtil.close(this.conn, stmt, rs);
			
		}
		
		return null;
	}
	
	public List<Transacoes> getPagamentosFazer(int idConta) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(PAG_FAZER);
			stmt.setInt(1, idConta);
			rs = stmt.executeQuery();
			List<Transacoes> lt = new ArrayList<Transacoes>();
			while(rs.next()){
				Transacoes t = new Transacoes();
				t.setId(rs.getInt(1));
				t.setTitulo(rs.getString(2));
				t.setValor(rs.getDouble(3));
				t.setData(rs.getDate(4));
				t.setDescricao(rs.getString(5));
				t.setTipo(rs.getString(6));
				t.setConta(ContaService.findById(rs.getInt(7)));
				t.setItemCategoria(ItensCategoriaService.findById(rs.getInt(8)));
				lt.add(t);
			}
			if(!lt.isEmpty()){
				return lt;
			}
			
		}catch (Exception e) {
			throw new Exception("Não foi possível recuperar os dados do banco." + e.getLocalizedMessage());
		}finally{
			DAOUtil.close(this.conn, stmt, rs);
			
		}
		
		return null;
	}
	
	public List<Map> getPagamanetosFazer(int idConta) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(PAG_FAZER);
			stmt.setInt(1, idConta);
			rs = stmt.executeQuery();
			List<Map> pagamentos = new ArrayList<Map>();
			while(rs.next()){
				Map mapa = new HashMap();
				Transacoes t = new Transacoes();
				t.setId(rs.getInt("TRN_ID"));
				t.setData(rs.getDate("TRN_DATA"));
				t.setValor(rs.getDouble("TRN_VALOR"));
				t.setTitulo(rs.getString("TRN_TITULO"));
				int diasResto = rs.getInt("DIAS FALTANTES");
				setTr(t);
				setDias(diasResto);
				pagamentos.add(addMap());
			}
			if(!pagamentos.isEmpty()){
				return pagamentos;
			}
		}catch (Exception e) {
			throw new Exception(e.getLocalizedMessage());
		}
		return null;
	}
	
	public List<Map> getLancamentosFuturos(int idConta) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(LANC_FUTURO);
			stmt.setInt(1, idConta);
			rs = stmt.executeQuery();
			List<Map> lancamentos = new ArrayList<Map>();
			while(rs.next()){				
				Transacoes t = new Transacoes();
				t.setId(rs.getInt("TRN_ID"));
				t.setData(rs.getDate("TRN_DATA"));
				t.setValor(rs.getDouble("TRN_VALOR"));
				t.setTitulo(rs.getString("TRN_TITULO"));
				int diasResto = rs.getInt("DIAS FALTANTES");
				setTr(t);
				setDias(diasResto);
				lancamentos.add(addMap());
			}
			if(!lancamentos.isEmpty()){
				return lancamentos;
			}
		}catch (Exception e) {
			throw new Exception(e.getLocalizedMessage());
		}
		return null;
	}
	
	private Map addMap(){
		Map mp = new HashMap();
		mp.put("transacao", tr);
		mp.put("dias", dias);
		
		return mp;
	}

	
	
}

	